package com.mva.Customer_DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mva.database.DB_Pipe;

public class CustomerDB implements CustomerDAO
  {
   private Connection con = null;
	   
   public CustomerDB()
	 {
	 }

   /*******************************************************************************
	Customer reader methods
   *******************************************************************************/
	   
   public boolean existsCustomer (int cust)
     {
	  if (cust < 1)
	    {
		 return false;
	    }
	  try
		{
	     String exists = "SELECT Cust "      +
				           "FROM customer "  +
				          "WHERE Cust  = ?";
         con                   = DB_Pipe.DB_Connect();
	     PreparedStatement ps  = con.prepareStatement(exists);
		 ps.setInt(1, cust);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
			DB_Pipe.DB_Disconnect(con);
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
			DB_Pipe.DB_Disconnect(con);
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
     }

   public boolean existsCustomer (String tin)
     {
	  String tmpTIN = tin.trim();
             tmpTIN = tmpTIN.toUpperCase();
	  try
		{
	     String exists = "SELECT Cust "      +
				           "FROM customer "  +
				          "WHERE TIN  = ?";
         con                   = DB_Pipe.DB_Connect();
	     PreparedStatement ps  = con.prepareStatement(exists);
		 ps.setString(1, tmpTIN);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
			DB_Pipe.DB_Disconnect(con);
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
			DB_Pipe.DB_Disconnect(con);
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
     }
   
   public Customer getCustomer (int cust)
     {
	  try
		{
		 String sel = "SELECT Cust, "   +
		                     "TIN, " +
		                     "Nm_Fst, " +
		                     "Nm_Lst, " +
		                     "Addr, " +
		                     "Phn, "    +
				             "Phn2, "   +
				             "Email, "  +
				             "Email2 "  +
				        "FROM customer "    +
				       "WHERE Cust = ?";
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(sel);
		 ps.setString(1, Customer);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    String TIN = rs.getString("TIN");
		    String Nm_Fst = rs.getString("Nm_Fst");
		    String Nm_Lst = rs.getString("Nm_Lst");
		    int Addr      = rs.getInt("Addr");
		    String Phn1   = rs.getString("Phn");
		    String Phn2   = rs.getString("Phn2");
		    String Email  = rs.getString("Email");
		    String Email2 = rs.getString("Email2");
		    Customer c        = new Customer(Customer, TIN, Nm_Fst, Nm_Lst, Addr, Phn1, Phn2, Email, Email2);
		    rs.close();
		    ps.close();
		    DB_Pipe.DB_Disconnect(con);
		    return u;
		   }
		 else
		   return null;
		}
		  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
     }
   
   public Customer getCustomer (String tin)
     {
	  try
		{
		 String sel = "SELECT Cust, "   +
		                     "TIN, " +
		                     "Nm_Fst, " +
		                     "Nm_Lst, " +
		                     "Addr, " +
		                     "Phn, "    +
				             "Phn2, "   +
				             "Email, "  +
				             "Email2 "  +
				        "FROM customer "    +
				       "WHERE tin = ?";
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(sel);
		 ps.setString(1, Customer);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    String TIN = rs.getString("TIN");
		    String Nm_Fst = rs.getString("Nm_Fst");
		    String Nm_Lst = rs.getString("Nm_Lst");
		    int Addr      = rs.getInt("Addr");
		    String Phn1   = rs.getString("Phn");
		    String Phn2   = rs.getString("Phn2");
		    String Email  = rs.getString("Email");
		    String Email2 = rs.getString("Email2");
		    Customer c        = new Customer(Customer, TIN, Nm_Fst, Nm_Lst, Addr, Phn1, Phn2, Email, Email2);
		    rs.close();
		    ps.close();
		    DB_Pipe.DB_Disconnect(con);
		    return u;
		   }
		 else
		   return null;
		}
		  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
     }
   
   public boolean getCustomers (ArrayList<Customer> custs)
     {
	  try
		{
		 String sel = "SELECT Cust, "   +
		                     "TIN, " +
		                     "Nm_Fst, " +
		                     "Nm_Lst, " +
		                     "Addr, " +
		                     "Phn, "    +
				             "Phn2, "   +
				             "Email, "  +
				             "Email2 "  +
				        "FROM customer "    +
				       "WHERE Cust = ?";
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(sel);
		 ps.setString(1, Customer);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    String TIN = rs.getString("TIN");
		    String Nm_Fst = rs.getString("Nm_Fst");
		    String Nm_Lst = rs.getString("Nm_Lst");
		    int Addr      = rs.getInt("Addr");
		    String Phn1   = rs.getString("Phn");
		    String Phn2   = rs.getString("Phn2");
		    String Email  = rs.getString("Email");
		    String Email2 = rs.getString("Email2");
		    Customer c        = new Customer(Customer, TIN, Nm_Fst, Nm_Lst, Addr, Phn1, Phn2, Email, Email2);
		    rs.close();
		    ps.close();
		    DB_Pipe.DB_Disconnect(con);
		    return u;
		   }
		 else
		   return null;
		}
		  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
     }

   /*******************************************************************************
	Customer writer methods
   *******************************************************************************/
   
   public boolean addCustomer (Customer c)
     {
	  try
		{
		 String ins = "INSERT "                   +
		                  "INTO customer (Cust, " +
		                            "TIN, "    +
		                            "Nm_Fst, "    +
		                            "Nm_Lst, "    +
		                            "Addr, "    +
		                            "Phn, "       +
		                            "Phn2, "      +
		                            "Email, "     +
		                            "Email2, "    +
		                            "TMS_Updt, "  +
		                            "User_Updt, " +
		                            "TMS_Crtd, "  +
		                            "User_Crtd) " +
				      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(ins);
	     Timestamp         ts  = DateUtils.getTmstmp();
		 ps.setString(1, c.getCustomer());
		 ps.setString(2, c.getTIN());
		 ps.setString(3, c.getNmFst());
		 ps.setString(5, c.getNmLst());
		 ps.setInt(6, c.getAddr());
		 ps.setString(7, c.getPhn1());
		 ps.setString(8, c.getPhn2());
		 ps.setString(9, c.getEmail());
		 ps.setString(10, c.getEmail2());
		 ps.setTimestamp(11, ts);
		 ps.setString(12, "USER1234");
		 ps.setTimestamp(13, ts);
		 ps.setString(14, "USER5678");
		 ps.executeUpdate();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
     }
   
   public boolean updateCustomer (Customer c)
     {
	  try
		{
		 String upd = "UPDATE customer "           +
		                 "SET TIN    = ?, " +
		                     "Nm_Fst    = ?, " +
		                     "Nm_Lst    = ?, " +
		                     "Addr      = ?, " +
		                     "Phn       = ?, " +
		                     "Phn2      = ?, " +
		                     "Email     = ?, " +
		                     "Email2    = ?, " +
		                     "TMS_Updt  = ?, " +
					         "User_Updt = ? "  +
				       "WHERE Customer = ?";
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(upd);
	     Timestamp         ts  = DateUtils.getTmstmp();
		 ps.setString(1, c.getTIN());
		 ps.setString(2, c.getNmFst());
		 ps.setString(4, c.getNmLst());
		 ps.setInt(5, c.getAddr());
		 ps.setString(6, c.getPhn1());
		 ps.setString(7, c.getPhn2());
		 ps.setString(8, c.getEmail());
		 ps.setString(9, c.getEmail2());
		 ps.setTimestamp(10, ts);
		 ps.setString(11, "USER1234");
		 ps.executeUpdate();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
     }
   
   public boolean deleteCustomer (Customer c)
     {
	  try
		{
		 String del = "DELETE "         +
		                "FROM customer "    +
				       "WHERE Cust = ?";
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(del);
		 ps.setString(1, c.getCustomer());
		 ps.executeUpdate();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}  
     }
  }
